using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VEKLEKL
    {
        public DataRow Convert2EKLEKL(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryEKLEKL(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "YGR":
                    #region=========
         dwReturn["EKLMANDANT"] = " ".PadRight(2 ); 
         dwReturn["EKLLEKLNR"] = " ".PadRight(8 ); 
         dwReturn["EKLAENZL"] = " ".PadRight(0 ); 
         dwReturn["EKLAENB"] = " ".PadRight(8 ); 
         dwReturn["EKLAEND"] = " ".PadRight(0 ); 
         dwReturn["EKLNEUB"] = " ".PadRight(8 ); 
         dwReturn["EKLNEUD"] = " ".PadRight(0 ); 
         dwReturn["EKLKUNDNR"] = " ".PadRight(8 ); 
         dwReturn["EKLWERK"] = " ".PadRight(3 ); 
         dwReturn["EKLABT"] = " ".PadRight(3 ); 
         dwReturn["EKLWAKZ"] = " ".PadRight(1 ); 
         dwReturn["EKLART"] = " ".PadRight(20 ); 
         dwReturn["EKLVAR"] = " ".PadRight(3 ); 
         dwReturn["EKLBHS"] = " ".PadRight(1 ); 
         dwReturn["EKLOART"] = " ".PadRight(20 ); 
         dwReturn["EKLOVAR"] = " ".PadRight(3 ); 
         dwReturn["EKLLIEFNR"] = " ".PadRight(8 ); 
         dwReturn["EKLLIEFLENR"] = " ".PadRight(10 ); 
         dwReturn["EKLLIEFLED"] = " ".PadRight(0 ); 
         dwReturn["EKLEINDAT"] = " ".PadRight(0 ); 
         dwReturn["EKLPREIS"] = " ".PadRight(0 ); 
         dwReturn["EKLPREH"] = " ".PadRight(3 ); 
         dwReturn["EKLPREHGRP"] = " ".PadRight(1 ); 
         dwReturn["EKLPREHFKT"] = " ".PadRight(12 ); 
         dwReturn["EKLPWAEHRSL"] = " ".PadRight(3 ); 
         dwReturn["EKLVONDAT"] = " ".PadRight(0 ); 
         dwReturn["EKLBISDAT"] = " ".PadRight(0 ); 
         dwReturn["EKLRECHNR"] = " ".PadRight(10 ); 
         dwReturn["EKLWIDERR"] = " ".PadRight(0 ); 
         dwReturn["EKLERLKZ"] = " ".PadRight(1 ); 
         dwReturn["EKLURLAND"] = " ".PadRight(3 ); 
         dwReturn["EKLWR"] = " ".PadRight(8 ); 
         dwReturn["EKLPRFBEG"] = " ".PadRight(1 ); 
         dwReturn["EKLTEXTSCHL"] = " ".PadRight(9 ); 
         dwReturn["EKLZOLLTNR"] = " ".PadRight(20 ); 
         dwReturn["EKLLIZENZME"] = " ".PadRight(0 ); 
         dwReturn["EKLTEXT1"] = " ".PadRight(50 ); 
         dwReturn["EKLTEXT2"] = " ".PadRight(50 ); 
         dwReturn["EKLFILLER"] = " ".PadRight(85 ); 
                    #endregion
                    break;
                case "JLT":

                    break;
                default:

                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }
        public void MapEKLEKL(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveEKLEKL(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryEKLEKL(dwData);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapEKLEKL(ref dwLocal, dwData);

            if (!flgExist)
            {

                InsertEKLEKL(dwLocal, ITXDBVERSION);
            }
            UpdateEKLEKL(dwLocal);
        }
        public void InsertEKLEKL(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "YGR":
                    or1.CommandText = "INSERT INTO EKLEKL(VAKBMANDANT, VAKBKOLL, VAKBART, VAKBKUNDNR, VAKBFARBE, VAKBKDARTNR, VAKBKDARTBEZ, VAKBKDFARBNR, VAKBANDRUCK, VAKBMEGEW, VAKBMENGEH, VAKBGEWVON, VAKBGEWBIS, VAKBGEWCA, VAKBGEWFKT, VAKBGEWGRP, VAKBMEGFKT, VAKBMEGGRP, VAKBAENDZL, VAKBAENDBEN, VAKBAENDDAT, VAKBNEUBEN, VAKBNEUDAT, VAKBFILLER)"
                        + " VALUES (:VAKBMANDANT, :VAKBKOLL, :VAKBART, :VAKBKUNDNR, :VAKBFARBE, '                         ', '                         ', '                             ', ' ', '   ', '   ', 0, 0, ' ', '000000000000', ' ', '000000000000', ' ', 0, '        ', '000000', '        ', '      ', '                                          ')";
                    or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                //VAFMANDANT VAFKOLL VAFART VAFFARBE VAFBEZ VAFFFARBE

            }
            
            Connect2Proxy(or1);
        }
        public DataSet QueryEKLEKL(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM EKLEKL WHERE  ROWNUM<2 ";
                    break;
              
                case 99:
                    or1.CommandText = "SELECT * FROM EKLEKL  WHERE ROWNUM<50";
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "EKLEKL";
            Return = Result;
            return Return;
        }
        public void DeleteEKLEKL(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM EKLEKL   WHERE  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                     or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        } 
      
        public void UpdateEKLEKL(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    #region====Command==== 
                    or1.CommandText = "UPDATE EKLEKL SET "
                    +"EKLMANDANT= :EKLMANDANT,"
                    +"EKLLEKLNR= :EKLLEKLNR,"
                    +"EKLAENZL= :EKLAENZL,"
                    +"EKLAENB= :EKLAENB,"
                    +"EKLAEND= :EKLAEND,"
                    +"EKLNEUB= :EKLNEUB,"
                    +"EKLNEUD= :EKLNEUD,"
                    +"EKLKUNDNR= :EKLKUNDNR,"
                    +"EKLWERK= :EKLWERK,"
                    +"EKLABT= :EKLABT,"
                    +"EKLWAKZ= :EKLWAKZ,"
                    +"EKLART= :EKLART,"
                    +"EKLVAR= :EKLVAR,"
                    +"EKLBHS= :EKLBHS,"
                    +"EKLOART= :EKLOART,"
                    +"EKLOVAR= :EKLOVAR,"
                    +"EKLLIEFNR= :EKLLIEFNR,"
                    +"EKLLIEFLENR= :EKLLIEFLENR,"
                    +"EKLLIEFLED= :EKLLIEFLED,"
                    +"EKLEINDAT= :EKLEINDAT,"
                    +"EKLPREIS= :EKLPREIS,"
                    +"EKLPREH= :EKLPREH,"
                    +"EKLPREHGRP= :EKLPREHGRP,"
                    +"EKLPREHFKT= :EKLPREHFKT,"
                    +"EKLPWAEHRSL= :EKLPWAEHRSL,"
                    +"EKLVONDAT= :EKLVONDAT,"
                    +"EKLBISDAT= :EKLBISDAT,"
                    +"EKLRECHNR= :EKLRECHNR,"
                    +"EKLWIDERR= :EKLWIDERR,"
                    +"EKLERLKZ= :EKLERLKZ,"
                    +"EKLURLAND= :EKLURLAND,"
                    +"EKLWR= :EKLWR,"
                    +"EKLPRFBEG= :EKLPRFBEG,"
                    +"EKLTEXTSCHL= :EKLTEXTSCHL,"
                    +"EKLZOLLTNR= :EKLZOLLTNR,"
                    +"EKLLIZENZME= :EKLLIZENZME,"
                    +"EKLTEXT1= :EKLTEXT1,"
                    +"EKLTEXT2= :EKLTEXT2,"
                    +"EKLFILLER= :EKLFILLER,"
                        + " WHERE  "
                        + "  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                    #endregion

                    #region====Parameters====
          or1.Parameters.Add("EKLMANDANT", OracleType.NVarChar).Value = dwData["EKLMANDANT"].ToString(); 
          or1.Parameters.Add("EKLLEKLNR", OracleType.NVarChar).Value = dwData["EKLLEKLNR"].ToString(); 
          or1.Parameters.Add("EKLAENZL", OracleType.NVarChar).Value = dwData["EKLAENZL"].ToString(); 
          or1.Parameters.Add("EKLAENB", OracleType.NVarChar).Value = dwData["EKLAENB"].ToString(); 
          or1.Parameters.Add("EKLAEND", OracleType.NVarChar).Value = dwData["EKLAEND"].ToString(); 
          or1.Parameters.Add("EKLNEUB", OracleType.NVarChar).Value = dwData["EKLNEUB"].ToString(); 
          or1.Parameters.Add("EKLNEUD", OracleType.NVarChar).Value = dwData["EKLNEUD"].ToString(); 
          or1.Parameters.Add("EKLKUNDNR", OracleType.NVarChar).Value = dwData["EKLKUNDNR"].ToString(); 
          or1.Parameters.Add("EKLWERK", OracleType.NVarChar).Value = dwData["EKLWERK"].ToString(); 
          or1.Parameters.Add("EKLABT", OracleType.NVarChar).Value = dwData["EKLABT"].ToString(); 
          or1.Parameters.Add("EKLWAKZ", OracleType.NVarChar).Value = dwData["EKLWAKZ"].ToString(); 
          or1.Parameters.Add("EKLART", OracleType.NVarChar).Value = dwData["EKLART"].ToString(); 
          or1.Parameters.Add("EKLVAR", OracleType.NVarChar).Value = dwData["EKLVAR"].ToString(); 
          or1.Parameters.Add("EKLBHS", OracleType.NVarChar).Value = dwData["EKLBHS"].ToString(); 
          or1.Parameters.Add("EKLOART", OracleType.NVarChar).Value = dwData["EKLOART"].ToString(); 
          or1.Parameters.Add("EKLOVAR", OracleType.NVarChar).Value = dwData["EKLOVAR"].ToString(); 
          or1.Parameters.Add("EKLLIEFNR", OracleType.NVarChar).Value = dwData["EKLLIEFNR"].ToString(); 
          or1.Parameters.Add("EKLLIEFLENR", OracleType.NVarChar).Value = dwData["EKLLIEFLENR"].ToString(); 
          or1.Parameters.Add("EKLLIEFLED", OracleType.NVarChar).Value = dwData["EKLLIEFLED"].ToString(); 
          or1.Parameters.Add("EKLEINDAT", OracleType.NVarChar).Value = dwData["EKLEINDAT"].ToString(); 
          or1.Parameters.Add("EKLPREIS", OracleType.NVarChar).Value = dwData["EKLPREIS"].ToString(); 
          or1.Parameters.Add("EKLPREH", OracleType.NVarChar).Value = dwData["EKLPREH"].ToString(); 
          or1.Parameters.Add("EKLPREHGRP", OracleType.NVarChar).Value = dwData["EKLPREHGRP"].ToString(); 
          or1.Parameters.Add("EKLPREHFKT", OracleType.NVarChar).Value = dwData["EKLPREHFKT"].ToString(); 
          or1.Parameters.Add("EKLPWAEHRSL", OracleType.NVarChar).Value = dwData["EKLPWAEHRSL"].ToString(); 
          or1.Parameters.Add("EKLVONDAT", OracleType.NVarChar).Value = dwData["EKLVONDAT"].ToString(); 
          or1.Parameters.Add("EKLBISDAT", OracleType.NVarChar).Value = dwData["EKLBISDAT"].ToString(); 
          or1.Parameters.Add("EKLRECHNR", OracleType.NVarChar).Value = dwData["EKLRECHNR"].ToString(); 
          or1.Parameters.Add("EKLWIDERR", OracleType.NVarChar).Value = dwData["EKLWIDERR"].ToString(); 
          or1.Parameters.Add("EKLERLKZ", OracleType.NVarChar).Value = dwData["EKLERLKZ"].ToString(); 
          or1.Parameters.Add("EKLURLAND", OracleType.NVarChar).Value = dwData["EKLURLAND"].ToString(); 
          or1.Parameters.Add("EKLWR", OracleType.NVarChar).Value = dwData["EKLWR"].ToString(); 
          or1.Parameters.Add("EKLPRFBEG", OracleType.NVarChar).Value = dwData["EKLPRFBEG"].ToString(); 
          or1.Parameters.Add("EKLTEXTSCHL", OracleType.NVarChar).Value = dwData["EKLTEXTSCHL"].ToString(); 
          or1.Parameters.Add("EKLZOLLTNR", OracleType.NVarChar).Value = dwData["EKLZOLLTNR"].ToString(); 
          or1.Parameters.Add("EKLLIZENZME", OracleType.NVarChar).Value = dwData["EKLLIZENZME"].ToString(); 
          or1.Parameters.Add("EKLTEXT1", OracleType.NVarChar).Value = dwData["EKLTEXT1"].ToString(); 
          or1.Parameters.Add("EKLTEXT2", OracleType.NVarChar).Value = dwData["EKLTEXT2"].ToString(); 
          or1.Parameters.Add("EKLFILLER", OracleType.NVarChar).Value = dwData["EKLFILLER"].ToString(); 
                    #endregion
                    break; 
                default:
                    break;
            }


            Connect2Proxy(or1);
        }
    }
}
